package c4.billing.cyclemanager;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


/**
 * This class is used to calculate the invoice for each customer for the billing cycle.
 */
public class InvoiceCalculation {
	
    public InvoiceCalculation(){
	
		try {
			Statement stmtAccounts, stmtPhones, stmtCharges, stmtRates;
			ResultSet rsAccounts, rsPhones, rsCharges, rsRates;
			String query, accountNum, phone, type;
			double calls, voicemail, threeWay, callerId, taxes, duration;
			double lineCharges, lineTaxes, bill;
			
			/*
			 * Initialize rates and charges
			 */
			calls = 0;
			voicemail = 0;
			threeWay = 0;
			callerId = 0;
			taxes = 0;
			lineCharges = 0;
			lineTaxes = 0;

			/*
			 * Setup database objects
			 */
			Class.forName("com.mysql.jdbc.Driver").newInstance();
			
		    Connection conn = DriverManager.getConnection("jdbc:mysql://localhost/corporate?" + 
		                                   "user=c4_user&password=p@ssw0rd");
		    /*
		     * Setup all the statement objects;
		     */
			stmtAccounts = conn.createStatement();
			stmtPhones = conn.createStatement();
			stmtCharges = conn.createStatement();
			stmtRates = conn.createStatement();
			
			/*
			 * Remove any information from charges and bill table so
			 * there are no primary key conflicts
			 */
			query = "DELETE FROM charges WHERE charges > -1;";
			
			stmtAccounts.execute(query);
			
			query = "DELETE FROM bills WHERE bill > -1;";
			
			stmtAccounts.execute(query);
			
			/**
			 * Set the rates
			 */
			query = "SELECT * FROM rates;";
			
			rsRates = stmtRates.executeQuery(query);
			
			while(rsRates.next()) {
				type = rsRates.getString("service_name");
				
				if(type.equals("call"))
					calls = rsRates.getDouble("dollars");
				else if(type.equals("caller_id"))
					callerId = rsRates.getDouble("dollars");
				else if(type.equals("taxes"))
					taxes = rsRates.getDouble("dollars");
				else if(type.equals("three_way"))
					threeWay = rsRates.getDouble("dollars");
				else
					voicemail = rsRates.getDouble("dollars");
			}
			
			/*
			 * Loop through Accounts in account_information table
			 */
			query =	"SELECT * FROM account_information;";
			rsAccounts = stmtAccounts.executeQuery(query);
			
			while(rsAccounts.next()) {
				/*
				 * Calculate charges for voicemail, threeway, and caller id
				 */
				bill = 0;
				
				if(rsAccounts.getString(Constants.VOICEMAIL).equals("Y"))
					bill += voicemail;
				
				if(rsAccounts.getString(Constants.CALLER_ID).equals("Y"))
					bill += callerId;

				if(rsAccounts.getString(Constants.THREE_WAY).equals("Y"))
					bill += threeWay;
				
				/*
				 * Loop through the phone lines associated with this account number
				 */
				
				accountNum = rsAccounts.getString(Constants.ACCOUNT_NUMBER);
				
				query = "SELECT phone_number FROM phone_number_listing " +
						"WHERE account_number=" + accountNum + ";";
				
				rsPhones = stmtPhones.executeQuery(query);
				
				while(rsPhones.next()) {
					phone = rsPhones.getString(Constants.PHONE_NUMBER);
					
					/*
					 * Loop through the calls associated with this phone number
					 */
					query = "SELECT duration " +
							"FROM call_log " +
							"WHERE phone_number=" + phone + ";";
					
					rsCharges = stmtCharges.executeQuery(query);
					
					duration = 0;
					
					/*
					 * Total up the charges using the rates set above, and
					 * calculate the tax
					 */
					while(rsCharges.next())
						duration += rsCharges.getDouble("duration");

					lineCharges = duration * calls;
					lineTaxes = lineCharges * taxes;
					
					lineCharges = round(lineCharges, 2);
					lineTaxes = round(lineTaxes, 2);
					
					/*
					 * Write the charges to the charges table
					 */
					query = "INSERT INTO charges (account_number, phone_number, charges, taxes) " +
							"VALUES (" + accountNum + "," + phone + "," + lineCharges +
							"," + lineTaxes + ");";
					
					stmtCharges.execute(query);
					
					bill += (lineTaxes + lineCharges);

				}
				
				/*
				 * Store bill amount in the bill table
				 */
				query = "INSERT INTO bills VALUES (" + accountNum + "," + bill + ");";
				stmtCharges.execute(query);
			}
	    }
		catch (SQLException ex) {
		    // handle any errors
		    System.out.println("SQLException: " + ex.getMessage());
		    System.out.println("SQLState: " + ex.getSQLState());
		    System.out.println("VendorError: " + ex.getErrorCode());
		}
		catch (Exception e) {
			System.out.println("Exception: " + e);
		}
	}
    
    /**
     * Returns a rounded double
     * 
     * @param d				double to round
     * @param decimalPlace	Number of decimal places to round the double
     * @return Rounded double
     */
    public static double round(double d, int decimalPlace){
        BigDecimal bd = new BigDecimal(Double.toString(d));
        bd = bd.setScale(decimalPlace,BigDecimal.ROUND_HALF_UP);
        return bd.doubleValue();
      }
}


